package com.panopset.db;

import java.beans.PropertyDescriptor;
import java.io.StringWriter;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Panopset Briefcase is free to academic organizations, non-profit
 * organizations, students, and poor people.
 *
 * If you use Panopset Briefcase in a corporate environment or consider yourself
 * a middle class or rich person, please <a
 * href="http://panopset.com/briefcase.html">donate</a> the 10 dollar individual
 * use annual fee, and/or the 100 dollar lifetime license per team member on
 * panopset.com depending upon which course of action is most suitable to your
 * situation, as defined by your own values and standards of ethical behavior.
 *
 * @author Karl Dinwiddie
 *
 * @param <T extends DataObject>
 *            POJO class that has getters and setters for each row field in the
 *            table.
 */
public abstract class TableDescription<T extends DataObject> {

    private String table_nm;
    private String schema_nm;
    private String simple_tbl_nm;
    protected final DBmgr dm;
    private List<DataField> flds;
    private List<DataField> ukcs;

    private List<DataField> getFlds() {
        if (flds == null) {
            flds = new ArrayList<DataField>();
            flds.add(getTableKeyField());
            populateFlields(flds);
        }
        return flds;
    }

    private List<DataField> getUkcs() {
        if (ukcs == null) {
            ukcs = new ArrayList<DataField>();
            populateUniqueKeyCombos(ukcs);
        }
        return ukcs;
    }

    public String getSchemaName() {
        return schema_nm;
    }

    public String getSimpleTableName() {
        return simple_tbl_nm;
    }

    public String getFullTableName() {
        if (table_nm == null) {
            StringWriter sw = new StringWriter();
            sw.append(getSchemaName());
            sw.append(".");
            sw.append(getSimpleTableName());
            table_nm = sw.toString();
        }
        return table_nm;
    }

    private final FieldIdKey tableKeyField = new FieldIdKey("id");

    public FieldIdKey getTableKeyField() {
        return tableKeyField;
    }

    private FieldIdKey tableBindField;

    public FieldIdKey getTableBindField() {
        if (tableBindField == null) {
            StringWriter sw = new StringWriter();
            sw.append(getSimpleTableName());
            tableBindField = new FieldIdKey(sw.toString());
        }
        return tableBindField;
    }

    protected abstract void populateFlields(List<DataField> flds);

    protected void populateUniqueKeyCombos(List<DataField> ukcs) {
    }

    private final Class<T> clazz;

    /**
     *
     * @param dataClass
     *            Java Class represented by this table.
     * @param schemaName
     *            Schema name.
     * @param simpleTableName
     *            Simple table name (no schema included).
     * @param dataStore
     */
    public TableDescription(final Class<T> dataClass, final String schemaName,
            final String simpleTableName) {
        clazz = dataClass;
        schema_nm = schemaName;
        simple_tbl_nm = simpleTableName;
        dm = new DBmgr();
        if (!tables.contains(getFullTableName())) {
            dm.execNoParamSql(getTableCreateSql());
            tables.add(getFullTableName());
        }
    }

    private static final List<String> tables = new ArrayList<String>();

    private String getTableCreateSql() {
        StringWriter sw = new StringWriter();
        sw.append("create table if not exists ");
        sw.append(getFullTableName());
        sw.append(" (\n ");
        for (DataField fld : getFlds()) {
            sw.append(fld.getCreateTableCmd());
            sw.append(",\n ");
            if (fld instanceof FieldIdBinder) {
                FieldIdBinder fib = (FieldIdBinder) fld;
                if (!tables.contains(fib.getBoundTable().getFullTableName())) {
                    dm.execNoParamSql(fib.getBoundTable().getTableCreateSql());
                    tables.add(fib.getBoundTable().getFullTableName());
                }
            }
        }
        boolean hasComma = true;
        for (DataField fld : getFlds()) {
            if (fld.isPrimaryKey()) {
                if (!hasComma) {
                    sw.append(",\n");
                }
                sw.append(" primary");
                if (fld.isUnique()) {
                    sw.append(" unique");
                }
                sw.append(" key (");
                sw.append(fld.getName());
                sw.append(")");
                hasComma = false;
            }
        }
        for (DataField fld : getFlds()) {
            if (fld.hasAdditionalAttributes()) {
                if (!hasComma) {
                    sw.append(",\n");
                }
                sw.append(fld.getAdditionalAttributes());
                hasComma = false;
            }
        }
        for (DataField fld : getFlds()) {
            if (fld.isIndexed()) {
                if (!hasComma) {
                    sw.append(",\n");
                }
                if (fld.isUnique()) {
                    sw.append(" unique");
                }
                sw.append(" index(");
                sw.append(fld.getName());
                sw.append(")");
                hasComma = false;
            }
        }
        if (!getUkcs().isEmpty()) {
            if (!hasComma) {
                sw.append(",\n");
            }
            sw.append(" unique key (");
            hasComma = true;
            for (DataField uk : getUkcs()) {
                if (!hasComma) {
                    sw.append(",");
                }
                sw.append(uk.getName());
                hasComma = false;
            }
            sw.append(")\n");
            hasComma = false;
        }
        sw.append("\n);\n");
        return sw.toString();
    }

    public T map2Object(Map<String, Object> fmap) {
        if (fmap == null || fmap.isEmpty()) {
            return null;
        }
        StringWriter sw = new StringWriter();
        try {
            T rtn = clazz.newInstance();
            sw.append("Mapping ");
            sw.append(fmap.toString());
            sw.append(" to \n");
            sw.append(clazz.getCanonicalName());
            sw.append("\n");
            for (DataField fld : getFlds()) {
                if (fld instanceof FieldIdBinder) {
                    FieldIdBinder fldIdBinder = (FieldIdBinder) fld;
                    TableDescription<?> boundTable = fldIdBinder
                            .getBoundTable();
                    String mapKey = fld.getName();
                    Object key = fmap.get(mapKey);
                    DataObject boundDataObject = boundTable.getByIndex(
                            boundTable.getTableKeyField(), key);
                    Method method = new PropertyDescriptor(fld.getName(), clazz)
                            .getWriteMethod();
                    sw.append("invoking ");
                    sw.append(method.getName());
                    sw.append("\n on \n");
                    sw.append(rtn.getClass().getCanonicalName());
                    method.invoke(rtn, boundDataObject);
                } else {
                    new PropertyDescriptor(fld.getName(), clazz)
                            .getWriteMethod().invoke(rtn,
                                    fmap.get(fld.getName()));
                }
            }
            return rtn;
        } catch (Exception ex) {
            throw new RuntimeException(sw.toString(), ex);
        }
    }

    public String getSelectAllSql() {
        StringWriter sw = new StringWriter();
        sw.append("select * from ");
        sw.append(getFullTableName());
        return sw.toString();
    }

    /**
     * Update existing row, this is NOT an upsert.
     *
     * @param idx
     *            Indexes. Unique indexes to be used to find an existing row to
     *            update.
     * @param row
     *            Object to be written.
     * @param dataFields
     *            fields to be updated.
     */
    public void updateByIndex(final DataField[] idx, final T row,
            final DataField... dataFields) {
        if (idx == null || idx.length < 1) {
            return;
        }
        if (dataFields == null || dataFields.length < 1) {
            return;
        }
        StringWriter sw = new StringWriter();
        sw.append("update ");
        sw.append(getFullTableName());
        sw.append(" set ");
        boolean needsComma = false;
        List<Object> params = new ArrayList<Object>();
        for (DataField fld : dataFields) {
            if (needsComma) {
                sw.append(",");
            }
            sw.append(fld.getName());
            sw.append(" = ?");
            needsComma = true;
            addParameterForUpdate(row, params, fld);
        }
        sw.append(" where ");
        needsComma = false;
        for (DataField fld : idx) {
            if (needsComma) {
                sw.append(" and ");
            }
            sw.append(fld.getName());
            sw.append(" = ?");
            addParameterForUpdate(row, params, fld);
        }
        sw.append("\n");
        SqlSpec dc = new SqlSpec();
        int i = 0;
        for (Object o : params) {
            i = i + 1;
            dc.setObject(i, o);
        }
        dc.setSql(sw.toString());
        dm.exec(dc);
    }

    private Object getReflection(final DataObject row, DataField fld) {
        try {
            return getReflectedValue(row, fld);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private Object getReflectedValue(final DataObject row, DataField fld)
            throws Exception {
        Object resObject = null;
        if (fld instanceof FieldIdBinder) {
            FieldIdBinder fib = (FieldIdBinder) fld;
            TableDescription<?> bt = fib.getBoundTable();
            DataObject boundDataObject = (DataObject) new PropertyDescriptor(
                    bt.getSimpleTableName(), clazz).getReadMethod().invoke(row);
            long id = boundDataObject.getId();
            if (id == -1) {
                id = bt.getIdByIndexes(boundDataObject);
            }
            if (id == -1) {
                id = bt.insert(boundDataObject);
            }
            resObject = id;
        } else {
            resObject = new PropertyDescriptor(fld.getName(), clazz)
                    .getReadMethod().invoke(row);
            if (resObject instanceof Object[]) {
                if (((Object[]) resObject).length == 1) {
                    resObject = ((Object[]) resObject)[0];
                }
            }
        }
        return resObject;
    }

    private void addParameterForUpdate(final DataObject row,
            List<Object> params, DataField fld) {
        try {
            params.add(getReflection(row, fld));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     *
     * @param dobj
     *            DataObject with keys populated.
     * @return -1 if not found, otherwise the id of the found object
     */
    public long getIdByIndexes(DataObject row) {
        StringWriter sw = new StringWriter();
        sw.append("select id from ");
        sw.append(getFullTableName());
        sw.append(" where ");
        List<DataField> indexes = new ArrayList<DataField>();
        for (DataField fld : getFlds()) {
            if (fld.isIndexed() && !"id".equals(fld.getName())) {
                indexes.add(fld);
            }
        }
        boolean needsComma = false;
        for (DataField idf : indexes) {
            if (needsComma) {
                sw.append(" and ");
            } else {
                needsComma = true;
            }
            sw.append(idf.getName());
            sw.append(" = ?");
        }
        SqlSpec dc = new SqlSpec();
        dc.setSql(sw.toString());
        int i = 0;
        for (DataField idf : indexes) {
            Object obj = getReflection(row, idf);
            if (obj == null) {
                return -1;
            }
            i = i + 1;
            dc.setObject(i, obj);
        }
        List<Map<String, Object>> result = dm.qry(dc);
        if (result == null || result.size() < 1) {
            return -1;
        }
        return (Long) result.get(0).get("id");
    }

    public long insert(final DataObject row) {
        StringWriter sw = new StringWriter();
        sw.append("insert into ");
        sw.append(getFullTableName());
        sw.append("\n (");
        boolean needsComma = false;
        for (DataField fld : getFlds()) {
            if (fld instanceof FieldIdKey) {
                continue;
            }
            if (needsComma) {
                sw.append(",");
            }
            needsComma = true;
            sw.append(fld.getName());
        }
        sw.append("\n) values (");
        sw.append("");
        List<Object> params = new ArrayList<Object>();
        needsComma = false;
        for (DataField fld : getFlds()) {
            if (fld instanceof FieldIdKey) {
                continue;
            }
            if (needsComma) {
                sw.append(",");
            }
            sw.append("?");
            needsComma = true;
            addParameterForUpdate(row, params, fld);
        }
        sw.append("\n)");
        SqlSpec dc = new SqlSpec();
        dc.setSql(sw.toString());
        int i = 0;
        for (Object o : params) {
            i = i + 1;
            dc.setObject(i, o);
        }
        return dm.execUpdate(dc, getTableKeyField().getName());
    }

    /**
     *
     * @param key
     *            indexed DataField.
     * @return WARNING, will return null if Object does not exist.
     */
    public T getByIndex(final DataField dataField, Object value) {
        SqlSpec dc = new SqlSpec();
        dc.setSql(getByIndexSql(dataField));
        dc.setObject(1, value);
        List<Map<String, Object>> result = dm.qry(dc);
        if (result == null || result.size() < 1) {
            return null;
        }
        return map2Object(result.get(0));
    }

    private String getByIndexSql(final DataField domainfield) {
        StringWriter sw = new StringWriter();
        sw.append("select * from ");
        sw.append(getFullTableName());
        sw.append(" where ");
        sw.append(domainfield.getName());
        sw.append(" = ?");
        return sw.toString();
    }

    public static final long ONE_MINUTE = 1000 * 60;
}
